﻿using BCCommon;
using BCEntity.TMS.Packing.PackingConfig;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.TMS.Packing.PackingConfig
{
    public class PackingConfigData : IPackingConfigData
    {
        private readonly IDatabaseContext database; 

        private const string QUERY = @"SELECT
		                                A.PackingConfigId,
		                                A.PackingCategoryConfigId,
		                                A.PackingTitle,
		                                A.PackingWidth,
		                                A.PackingLength,
		                                A.PackingHeight,
		                                A.DimensionUnit,
		                                A.ShowUnit,
		                                A.TransformRatio,
		                                A.Price,
		                                A.Enabled,
		                                A.CreateAdminId,
		                                A.CreateAdminName,
		                                A.CreateTime,
		                                A.UpdateTime,
		                                A.EditAdminId,
		                                A.EditAdminName 
                                    FROM
                                        PackingConfig A";

        private const string QUERY_JOIN = @"SELECT
		                                A.PackingConfigId,
		                                A.PackingCategoryConfigId,
		                                A.PackingTitle,
		                                A.PackingWidth,
		                                A.PackingLength,
		                                A.PackingHeight,
		                                A.DimensionUnit,
		                                A.ShowUnit,
		                                A.TransformRatio,
		                                A.Price,
		                                A.Enabled,
		                                A.CreateAdminId,
		                                A.CreateAdminName,
		                                A.CreateTime,
		                                A.UpdateTime,
		                                A.EditAdminId,
		                                A.EditAdminName,
                                        B.PackingCategory 
                                    FROM PackingConfig A
                                    INNER JOIN PackingCategoryConfig B ON A.PackingCategoryConfigId = B.PackingCategoryConfigId ";

        #region 构造函数
        public PackingConfigData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新包装材料配置纪录
        /// </summary>
        /// <param name="packingConfigEntity"></param>
        /// <returns></returns>
        public async Task<PackingConfigEntity> InsertAsync(PackingConfigEntity entity)
        {
            return await this.database.Master.InsertAsync(entity);
        }
        #endregion

        public async Task<(IEnumerable<PackingConfigViewEntity>, int)> Query(int? packingCategoryConfigId, string packingTitle, bool? enabled, DateTime? createStartDate, DateTime? createEndDate, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (packingCategoryConfigId.HasValue)
            {
                wheres.Add("A.PackingCategoryConfigId = ?PackingCategoryConfigId");
                parameters.Add("?PackingCategoryConfigId", packingCategoryConfigId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(packingTitle))
            {
                wheres.Add("A.PackingTitle LIKE ?PackingTitle");
                parameters.Add("?PackingTitle", $"%{packingTitle}%", DbType.AnsiString);
            }
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            if (createStartDate.HasValue)
            {
                wheres.Add("A.CreateTime >= ?CreateStartDate");
                parameters.Add("?CreateStartDate", createStartDate.Value.Date, DbType.Date);
            }
            if (createEndDate.HasValue)
            {
                wheres.Add("A.CreateTime < ?CreateEndDate");
                parameters.Add("?CreateEndDate", createEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.PackingConfigId,A.PackingCategoryConfigId DESC LIMIT {2},{3};
                                        SELECT COUNT(1) FROM PackingConfig A {1}",
                                        QUERY_JOIN,
                                        wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                                        (pageIndex - 1) * pageSize,
                                        pageSize);
            return await database.Slave.QueryPageableListAsync<PackingConfigViewEntity>(sql, parameters);
        }

        public async Task<PackingConfigEntity> GetEntityByCategoryConfigId(int packingCategoryConfigId)
        {
            return await database.Slave.QueryEntityAsync<PackingConfigEntity>($"{QUERY} WHERE A.PackingCategoryConfigId = ?PackingCategoryConfigId LIMIT 1", new { packingCategoryConfigId });
        }

        public async Task<PackingConfigEntity> GetEntity(long packingConfigId)
        {
            return await database.Slave.QueryEntityAsync<PackingConfigEntity>($"{QUERY} WHERE A.PackingConfigId = ?PackingConfigId", new { packingConfigId });
        }

        public async Task<int> Delete(int packingConfigId)
        {
            return await database.Master.ExecuteAsync("DELETE FROM PackingConfig WHERE PackingConfigId = ?PackingConfigId", new { packingConfigId });
        }

        public async Task<IEnumerable<PackingConfigViewEntity>> GetList(int? packingCategoryConfigId, string packingTitle, bool? enabled)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (packingCategoryConfigId.HasValue)
            {
                wheres.Add("A.PackingCategoryConfigId = ?PackingCategoryConfigId");
                parameters.Add("?PackingCategoryConfigId", packingCategoryConfigId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(packingTitle))
            {
                wheres.Add("A.PackingTitle LIKE ?PackingTitle");
                parameters.Add("?PackingTitle", $"%{packingTitle}%", DbType.AnsiString);
            }
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            string sql = string.Format("{0} {1} ORDER BY A.PackingConfigId DESC", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await database.Slave.QueryAsync<PackingConfigViewEntity>(sql, parameters);
        }

        public async Task<PackingConfigEntity> Update(PackingConfigEntity entity)
        {
            return await database.Master.UpdateAsync(entity);
        }
    }
}
